CS157b Fall 2002Practice Final

The practice final is below. Here are some facts about the actual final: (a) The final will be in class Dec 13 12:15pm-2:30pm (Sec 1) and Dec 17 12:15pm-2:30pm (Sec 2). (b) It is closed book, closed notes. Nothing will be permitted on your desk except your pen (pencil) and test. (c) You should bring photo ID. (d) There will be more than one version of the test. Each version will be of comparable difficulty. (e) If your cell-phone or beeper goes off you will be excused from the test at that point and graded on what you have done till your excusal. (f) One problem (less typos) on the actual test will be from the practice test.

1.  Write a short SQLJ program to connect to Oracle and print out all
the rows satisfying the query:

SELECT FNAME, LNAME
FROM EMPLOYEE WHERE SALARY>20000

Make sure to set the transaction isolation level in your program to 
the level best suited to this task. 

2. What is a dirty read? What is a nonrepeatable read?

3. Give a schedule that could occur if the basic timestamp ordering algorithm
is used but could not occur if two phase locking is used.

4. Give a schedule that could occur if two phase locking is used but could 
not occur if  the basic timestamp ordering algorithm is used.

5. Starvation occurs when a transaction never gets 
to proceed while other transaction in the system get to proceed normally. For instance,
if many transaction vie for the same lock, and new transactions keep arriving wanting 
this lock, it might occur that some transaction never gets its turn to get the lock
even though it has waited for a long time. How is starvation avoided in the Wait-die 
and Wound-wait protocols?

6. What levels of data granularity for locking are available in Oracle?

7. In terms of DBMS recovery what is a checkpoint? What is pinning? What is a no-steal
approach for writing cached blocks?

8. Give a recovery algorithm that works in the case that immediate updates are
used.

9. Give a recovery algorithm that works if pages are shadowed.

10. Let T be a table with columns X, Y, Z. How could one grant to the public, access
only to the columns X and Y and only for those X values where X >10?